﻿USE NLLDatabase
--GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--CREATE FUNCTION fn_loc_dau
--(
--      @strInput NVARCHAR(4000)
--) 
--RETURNS NVARCHAR(4000)
--AS
--Begin
--	Set @strInput=rtrim(ltrim(lower(@strInput)))
--    IF @strInput IS NULL RETURN @strInput
--    IF @strInput = '' RETURN @strInput
--    Declare @text nvarchar(50), @i int
--    Set @text='-''`~!@#$%^&*()?><:|}{,./\"''='';–'
--    Select @i= PATINDEX('%['+@text+']%',@strInput ) 
--    while @i > 0
--        begin
--	        set @strInput = replace(@strInput, substring(@strInput, @i, 1), '')
--	        set @i = patindex('%['+@text+']%', @strInput)
--        End
--        Set @strInput =replace(@strInput,'  ',' ')
        
--    DECLARE @RT NVARCHAR(4000)
--    DECLARE @SIGN_CHARS NCHAR(136)
--    DECLARE @UNSIGN_CHARS NCHAR (136)
--    SET @SIGN_CHARS = N'ăâđêôơưàảãạáằẳẵặắầẩẫậấèẻẽẹéềểễệế
--                  ìỉĩịíòỏõọóồổỗộốờởỡợớùủũụúừửữựứỳỷỹỵý'
--                  +NCHAR(272)+ NCHAR(208)
--    SET @UNSIGN_CHARS = N'aadeoouaaaaaaaaaaaaaaaeeeeeeeeee
--                  iiiiiooooooooooooooouuuuuuuuuuyyyyy'
--    DECLARE @COUNTER int
--    DECLARE @COUNTER1 int
--    SET @COUNTER = 1
--    WHILE (@COUNTER <=LEN(@strInput))
--    BEGIN   
--      SET @COUNTER1 = 1
--       WHILE (@COUNTER1 <=LEN(@SIGN_CHARS)+1)
--       BEGIN
--     IF UNICODE(SUBSTRING(@SIGN_CHARS, @COUNTER1,1)) 
--            = UNICODE(SUBSTRING(@strInput,@COUNTER ,1) )
--     BEGIN           
--          IF @COUNTER=1
--              SET @strInput = SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1) 
--              + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)-1)                   
--          ELSE
--              SET @strInput = SUBSTRING(@strInput, 1, @COUNTER-1) 
--              +SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1) 
--              + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)- @COUNTER)
--              BREAK
--               END
--             SET @COUNTER1 = @COUNTER1 +1
--       END
--       SET @COUNTER = @COUNTER +1
--    End
-- SET @strInput = replace(@strInput,' ','-')
--    RETURN lower(@strInput)
--End


GO
CREATE VIEW view_UI_ALLArticle
AS
	SELECT A.[ID]
      ,A.[MenuID]
      ,M.[Name]
      ,A.[Title]
      ,A.[ThumbImage]
      ,A.[Description]
      ,A.[Content]
      ,A.[CreateDate]
      ,A.[IsSilde]
      ,A.[IsFrontPage]
      ,A.[AuthorID]
      ,Au.[Description] AS AuDescription
      ,A.[MetaTitle]
      ,A.[MetaDescription]
      ,A.[MetaKeywords]
      ,A.[MetaRobots]
      ,A.[MetaAuthor]
  FROM [Article] A LEFT JOIN Menu M ON A.MenuID = M.ID 
  LEFT JOIN Author Au ON A.AuthorID = Au.ID

GO 
CREATE VIEW view_GetCourseNames
AS
SELECT     C.ID, C.Name, C.[Content], C.Quantities, C.StartDate, C.Schedule, C.TimeSlot, C.IsFrontPage, C.Status, R.ID AS RegisterID, R.CheckInID, R.CourseID
FROM         Course AS C INNER JOIN
                      RegisterCourse AS R ON R.CourseID = C.ID
GO
--CREATE PROCEDURE sp_Search
--@Keyword nvarchar(MAX)
--AS
--BEGIN
-- SELECT ID, MenuID, Name, Title, ThumbImage, [Description], Content, CreateDate, AuthorID, AuDescription FROM view_UI_ALLArticle WHERE Title Like @Keyword OR Content Like @Keyword OR [Description] LIKE @Keyword
--END
